%%javascript
function toggler(){
    if(window.already_toggling){
        // Don't add multiple buttons.
        return 0
    }
    let btn = $('.input').append('<button>Toggle Code</button>')
        .children('button');
    btn.on('click', function(e){
        let tgt = e.currentTarget;
        $(tgt).parent().children('.inner_cell').toggle()
    })
    window.already_toggling = true;
}
// Since javascript cells are executed as soon as we load
// the notebook (if it's trusted), and this cell might be at the
// top of the notebook (so is executed first), we need to
// allow time for all of the other code cells to load before
// running. Let's give it 5 seconds.

setTimeout(toggler, 5000);

from IPython.display import HTML

HTML('''<script>
code_show=true; 
function code_toggle() {
 if (code_show){
 $('div.input').hide();
 } else {
 $('div.input').show();
 }
 code_show = !code_show
} 
$( document ).ready(code_toggle);
</script>
<form action="javascript:code_toggle()"><input type="submit" value="Toggle Code"></form>''')

import pandas as pd
import numpy as np

import plotly.express as px
import plotly

from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"

locs = pd.read_csv('data/hospital-locations.csv')
cr = pd.read_csv('data/hcris-2010-2018.csv').drop(columns=['Unnamed: 0'])

locs = locs.rename(columns=str.lower)

cr.shape
locs.shape

(46753, 27)

(202, 10)

cr['year'].unique()
cr['provider_number'].nunique()

array([2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018])

6607

locs['provider_name'] = locs['title'].str.upper()
cr['zip'] =  cr.zip.apply(lambda x: x[0:5])

df = pd.merge(cr,locs, on=['provider_name','zip'], how='inner')
df = df.sort_values(by=['provider_name','year'])
df

	report	provider_number	npi	fy_start	fy_end	date_processed	date_created	status	year	beds	...	provider_name	type	title	address1	city_y	state_y	latitude	longitude	primaryphone	buildingtype
460	632378.0	20017	NaN	01/01/2011	12/31/2011	09/17/2018	09/11/2018	4	2011	181.0	...	ALASKA REGIONAL HOSPITAL	Building	Alaska Regional Hospital	2801 DeBarr Road	Anchorage	AK	61.209805	-149.829200	(907) 276-1131	[Hospital]
461	560962.0	20017	NaN	01/01/2012	12/31/2012	09/29/2015	09/29/2015	2	2012	182.0	...	ALASKA REGIONAL HOSPITAL	Building	Alaska Regional Hospital	2801 DeBarr Road	Anchorage	AK	61.209805	-149.829200	(907) 276-1131	[Hospital]
462	596846.0	20017	NaN	01/01/2013	12/31/2013	05/16/2017	05/16/2017	4	2013	138.0	...	ALASKA REGIONAL HOSPITAL	Building	Alaska Regional Hospital	2801 DeBarr Road	Anchorage	AK	61.209805	-149.829200	(907) 276-1131	[Hospital]
463	631546.0	20017	NaN	01/01/2014	12/31/2014	08/22/2018	08/21/2018	4	2014	116.0	...	ALASKA REGIONAL HOSPITAL	Building	Alaska Regional Hospital	2801 DeBarr Road	Anchorage	AK	61.209805	-149.829200	(907) 276-1131	[Hospital]
464	619133.0	20017	NaN	01/01/2015	12/31/2015	02/16/2018	02/15/2018	2	2015	132.0	...	ALASKA REGIONAL HOSPITAL	Building	Alaska Regional Hospital	2801 DeBarr Road	Anchorage	AK	61.209805	-149.829200	(907) 276-1131	[Hospital]
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
345	569020.0	100228	NaN	02/01/2013	01/31/2014	01/20/2016	01/05/2016	2	2013	224.0	...	WESTSIDE REGIONAL MEDICAL CENTER	Building	Westside Regional Medical Center	8201 W Broward Blvd	Plantation	FL	26.123543	-80.259512	(954) 473-6600	[Hospital]
346	631559.0	100228	NaN	02/01/2014	01/31/2015	08/22/2018	08/21/2018	4	2014	224.0	...	WESTSIDE REGIONAL MEDICAL CENTER	Building	Westside Regional Medical Center	8201 W Broward Blvd	Plantation	FL	26.123543	-80.259512	(954) 473-6600	[Hospital]
347	640702.0	100228	NaN	02/01/2015	01/31/2016	01/24/2019	01/15/2019	4	2015	224.0	...	WESTSIDE REGIONAL MEDICAL CENTER	Building	Westside Regional Medical Center	8201 W Broward Blvd	Plantation	FL	26.123543	-80.259512	(954) 473-6600	[Hospital]
348	635568.0	100228	NaN	02/01/2016	01/31/2017	11/14/2018	11/07/2018	1	2016	224.0	...	WESTSIDE REGIONAL MEDICAL CENTER	Building	Westside Regional Medical Center	8201 W Broward Blvd	Plantation	FL	26.123543	-80.259512	(954) 473-6600	[Hospital]
349	629687.0	100228	NaN	02/01/2017	01/31/2018	07/19/2018	07/18/2018	1	2017	224.0	...	WESTSIDE REGIONAL MEDICAL CENTER	Building	Westside Regional Medical Center	8201 W Broward Blvd	Plantation	FL	26.123543	-80.259512	(954) 473-6600	[Hospital]

518 rows × 36 columns

Visualization

df_recent = df.groupby(by='provider_number').agg('last').reset_index()
df_recent.head()

	provider_number	report	npi	fy_start	fy_end	date_processed	date_created	status	year	beds	...	provider_name	type	title	address1	city_y	state_y	latitude	longitude	primaryphone	buildingtype
0	20017	631694.0	NaN	01/01/2017	12/31/2017	08/27/2018	08/27/2018	5	2017	164.0	...	ALASKA REGIONAL HOSPITAL	Building	Alaska Regional Hospital	2801 DeBarr Road	Anchorage	AK	61.209805	-149.829200	(907) 276-1131	[Hospital]
1	50022	633531.0	NaN	05/01/2017	04/30/2018	10/15/2018	10/09/2018	1	2017	456.0	...	RIVERSIDE COMMUNITY HOSPITAL	Building	Riverside Community Hospital	4445 Magnolia Ave	Riverside	CA	33.976668	-117.381043	(951) 788-3000	[Hospital]
2	50125	628880.0	NaN	01/01/2017	12/31/2017	07/19/2018	06/27/2018	1	2017	258.0	...	REGIONAL MEDICAL CENTER OF SAN JOSE	Building	Regional Medical Center of San Jose	225 North Jackson Ave	San Jose	CA	37.362568	-121.849745	(408) 259-5000	[Hospital]
3	50380	629921.0	NaN	02/01/2017	01/31/2018	07/24/2018	07/23/2018	1	2017	300.0	...	GOOD SAMARITAN HOSPITAL	Building	Good Samaritan Hospital	2425 Samaritan Dr	San Jose	CA	37.252071	-121.946331	(408) 559-2011	[Hospital]
4	60032	629818.0	NaN	01/01/2017	12/31/2017	07/20/2018	06/30/2018	1	2017	231.0	...	ROSE MEDICAL CENTER	Building	Rose Medical Center	4567 E. 9th Ave	Denver	CO	39.732308	-104.933961	(303) 320-2121	[ER, Hospital]

5 rows × 36 columns

fig = px.scatter_mapbox(df_recent[df_recent.state_y == 'FL'], zoom=6,
                        lat='latitude', lon='longitude', mapbox_style="carto-positron",
                        color='tot_operating_exp', size='beds', opacity=.6,
                        hover_name='title',hover_data=['buildingtype', 'address1','city_y','state_y'],
                        color_discrete_sequence=plotly.colors.qualitative.Prism_r, 
                       )
fig = fig.update_layout(mapbox_style="carto-positron")

fig = fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})

fig.show('notebook')

df.groupby('title').get_group('Alaska Regional Hospital').mean()

report                     6.160721e+05
provider_number            2.001700e+04
npi                                 NaN
status                     3.714286e+00
year                       2.014000e+03
beds                       1.538571e+02
tot_charges                7.495390e+08
tot_discounts              5.172936e+08
tot_operating_exp          1.702572e+08
ip_charges                 5.105930e+07
icu_charges                5.098778e+07
ancillary_charges          3.902280e+08
tot_discharges             5.675714e+03
mcare_discharges           1.794714e+03
mcaid_discharges           8.987143e+02
tot_mcare_payment          3.410666e+07
secondary_mcare_payment    3.556300e+04
zip                        1.421557e+34
latitude                   6.120981e+01
longitude                 -1.498292e+02
dtype: float64

px.line(df.groupby(['title','year'])['tot_operating_exp'].median().reset_index(), 
        x='year', y='tot_operating_exp', color='title')